package com.csist.dao;

import com.csist.bean.Student;
import com.csist.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName StudentDao
 * @Description TODO
 * @Author User
 * @Date 2021/6/26 16:31
 * @ModifyDate 2021/6/26 16:31
 * @Version 1.0
 */
public class StudentDao {
    public List<Student> getStudentsByClassesId(int id){
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from student where c_id = ?";
        List<Student> students = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                students.add(new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getBoolean(4),rs.getInt(5)));
            }
            if (students.size()==0){
                students=null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return students;
    }
    //获取学生数据条数
    public int getStudentDataNumByC_id(int id){
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select count(*) from student where c_id = ?";
        int dataNum = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()){
                dataNum = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return dataNum;
    }

    public List<Student> getStudentsByPage(int id,int curPage,int dataNum){
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from student where c_id = ? limit ?,?";
        List<Student> students = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setInt(2,(curPage-1)*dataNum);
            ps.setInt(3,dataNum);
            rs = ps.executeQuery();
            while (rs.next()){
                students.add(new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getBoolean(4),rs.getInt(5)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
        return students;
    }
}
